import cx_Oracle


from DBUtils.PooledDB import PooledDB
from common.db.dbconfigs import *

'''
1.安装cx_Oracle.exe
2.pip install cx_Oracle. 安装exe之后可能不用pip install 了?默认install了?
3.配置oci路径. 安装exe之后可能不需要配置了?默认配置了?


这里好像应该配置oci的路径, 可以在环境变量Path最后追加,或者os.environ代码里面指定.
但是测试时环境变量和os.environ都去掉了依然执行正常,是因为通过exe安装的自己进行了配置吗?
#exe下载路径
https://pypi.org/project/cx-Oracle/5.3/#files
cx_Oracle-5.3-11g.win-amd64-py3.6-2.exe
'''

# os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
# os.environ['path'] = 'D:/Program Files/Navicat Premium/instantclient_11_2/'

# 数据库连接池
db_pools = {}


def execute(sql, config, dictionary=False, connection=None, autocommit=True):
    if not connection:
        connection = _connection(config)

    results = _execute(sql, connection, dictionary)
    if autocommit:
        connection.commit()
    return results


def _connection(config):
    pool_key = str(config)
    if pool_key not in db_pools:
        '''
        1.maxcached确定了实际连接(真实和数据库连接,不是被pooleddb封装的那个)的数量
        验证: set @s=1. 当maxcached=1时,所有线程都可以拿到. 当maxcached=2时,有的可以拿到,有的拿不到.
        
        2.获取的连接关闭不关闭都一样, 关闭了也只是标识可以被其他线程使用. 
        实际使用体验是这样的: 这个连接好像不是同时只被一个线程使用,所有线程都可以同时使用它,
        所有一个线程不释放连接, 不影响其他线程使用.
        验证: cursor.close()之后不关闭连接, 也不退出方法,休眠10s,同时3个线程执行这个语句.
        最终在休眠3s后所有的线程同时执行完了.
        '''

        pool = PooledDB(creator=cx_Oracle, mincached=1, maxcached=2, maxconnections=4, blocking=True,
                        user=config["user"],
                        password=config["password"],
                        dsn=config["server"])
        db_pools[pool_key] = pool

    return db_pools[pool_key].connection()


def _execute(sql, connection, dictionary=False):
    cursor = connection.cursor()

    cursor.execute(sql)
    results = None
    if sql.lstrip().upper().startswith("SELECT"):
        # if dictionary:
        #     # 当使用连接池时rowfactory失效
        #     def __makeDictFactory(cursor):
        #         columnNames = [d[0] for d in cursor.description]
        #
        #         def createRow(*args):
        #             return dict(zip(columnNames, args))
        #
        #         return createRow
        #
        #     cursor.rowfactory = __makeDictFactory(cur sor)
        results = cursor.fetchall()
        if dictionary:
            columnNames = [d[0] for d in cursor.description]
            results1 = []
            for r in results:
                r1 = {}
                for i in range(0, len(columnNames)):
                    r1[columnNames[i]] = r[i]
                results1.append(r1)
            results = results1
    cursor.close()
    return results


if __name__ == "__main__":
    for i in range(0, 100):
        sql = """
            SELECT * FROM  
            (  
            SELECT A.*, ROWNUM RN  
            FROM (
                select name from md_org
            ) A  
            WHERE ROWNUM < 19200  
            )  
            WHERE RN = %d  
        """ % (19100 + i)
        print()
        result = execute(sql, dictionary=True, config=mds)
        for r in result:
            print(r)
